package pro.jiug.kExcel.utils;

import java.io.*;
import java.net.URL;
import java.net.URLConnection;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.UUID;

import com.sun.corba.se.spi.activation.Server;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;


/**
 * Excel工具类
 *
 * @author hexin
 * @version 2.1
 * @date 10:20 2015-07-22
 */
public class ExcelUtil {

	private ExcelUtil() {}

	/** Excel 97-2003 工作簿最大列数 */
	public static final int XLS_MAX_COLUMN = 256;
	/** Excel 97-2003 工作簿最大行数 */
	public static final int XLS_MAX_ROW = 65536;

	// #######################################
	// #######根据文件路径和工作表名称读取######
	// #######################################

	/**
	 * {@link #readExcel(String, String, int, int, int, int)}
	 */
	public static List<List<String>> readExcel(String filepath, String sheetName, int startRow, int endCol)
			throws IOException {
		return readExcel(filepath, sheetName, startRow, XLS_MAX_ROW, 1, endCol);
	}

	/**
	 * 读取Excel工作表内容
	 *
	 * @param filepath
	 *        Excel文件路径
	 * @param sheetName
	 *        工作表名称
	 * @param startRow
	 *        开始行号，从1开始
	 * @param endRow
	 *        结束行号，包含当前行
	 * @param startCol
	 *        开始列号，从1开始
	 * @param endCol
	 *        结束列号，包含当前列
	 * @return 返回工作表内容，外层表示行，内层表示该行单元格的内容，空单元格返回<code>null</code>
	 */
	public static List<List<String>> readExcel(String filepath, String sheetName, int startRow, int endRow, int startCol, int endCol)
			throws IOException {
		return readExcel(getSheet(filepath, sheetName), startRow, endRow, startCol, endCol);
	}

	public static List<List<String>> readExcel(MultipartFile file, String sheetName, int startRow, int endRow, int startCol, int endCol)
			throws IOException {
		return readExcel(getSheet(file, sheetName), startRow, endRow, startCol, endCol);
	}

	// #######################################
	// #######根据文件路径和工作表索引读取######
	// #######################################

	/**
	 * {@link #readExcel(String, int, int, int, int, int)}
	 */
	public static List<List<String>> readExcel(String filepath, int sheetIndex, int startRow, int endCol)
			throws IOException {
		return readExcel(filepath, sheetIndex, startRow, XLS_MAX_ROW, 1, endCol);
	}

	/**
	 * 读取Excel工作表内容
	 *
	 * @param filepath
	 *        Excel文件路径
	 * @param sheetIndex
	 *        工作表索引，从0开始
	 * @param startRow
	 *        开始行号，从1开始
	 * @param endRow
	 *        结束行号，包含当前行
	 * @param startCol
	 *        开始列号，从1开始
	 * @param endCol
	 *        结束列号，包含当前列
	 * @return 返回工作表内容，外层表示行，内层表示该行单元格的内容，空单元格返回<code>null</code>
	 */
	public static List<List<String>> readExcel(String filepath, int sheetIndex, int startRow, int endRow, int startCol, int endCol)
			throws IOException {
		return readExcel(getSheet(filepath, sheetIndex), startRow, endRow, startCol, endCol);
	}

	// ##########################
	// #######根据工作表读取######
	// ##########################

	/**
	 * 读取Excel工作表内容
	 *
	 * @param sheet
	 *        Excel工作表
	 * @param startRow
	 *        开始行号，从1开始
	 * @param endRow
	 *        结束行号，包含当前行
	 * @param startCol
	 *        开始列号，从1开始
	 * @param endCol
	 *        结束列号，包含当前列
	 * @return 返回工作表内容，外层表示行，内层表示该行单元格的内容，空单元格返回<code>null</code>
	 */
	public static List<List<String>> readExcel(Sheet sheet, int startRow, int endRow, int startCol, int endCol) {
		if (sheet == null) {
			throw new NullPointerException("工作表为空");
		}
		if (endRow < startRow) {
			throw new IllegalArgumentException("结束行不能早于开始行");
		}
		if (endCol < startCol) {
			throw new IllegalArgumentException("结束列不能早于开始列");
		}

		startRow = startRow > 0 ? startRow - 1 : 0;
		startCol = startCol > 0 ? startCol - 1 : 0;

		int lastRowNum = sheet.getLastRowNum() + 1;// 最后一行

		endRow = Math.min(endRow, lastRowNum);
		endCol = Math.min(endCol, XLS_MAX_COLUMN);

		int columWidth = endCol - startCol;

		Row row = null;
		Cell cell = null;
		List<List<String>> result = new ArrayList<>();
		for (int i = startRow; i < endRow; i++) {
			List<String> rowList = new ArrayList<>(columWidth);
			result.add(rowList);
			row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			for (int j = startCol; j < endCol; j++) {
				cell = row.getCell(j);
//				String cellValue = getCellValue(cell);
//				rowList.add(getCellValue(cell) == null ? "":cellValue);
				rowList.add(getCellValue(cell));
			}
		}
		return result;
	}

	// #############################
	// ##########生成Excel##########
	// #############################

	/**
	 * 将数据转换为Excel 97-2003 工作簿
	 *
	 * @param <T> T extends Collection&lt;?&gt; or T extends Object[]
	 * @param data
	 *        Excel数据
	 * @param filepath
	 *        Excel文件绝对路径，如果已经存在则删除
	 * @param columnNames
	 *        列名
	 * @param columnWidths
	 *        列宽
	 * @return
	 * 		返回生成的本地Excel文件对象
	 */
	public static <T> File dataToExcel(Collection<T> data, String filepath, String[] columnNames, int[] columnWidths) {
		File file = new File(filepath);
		if (file.exists()) {
			file.delete();
		}
		String name = file.getName();
		int dotIndex = name.lastIndexOf(".");
		if (dotIndex != -1) {
			name = name.substring(0, dotIndex);
		}

		return dataToExcel(data, filepath, name, columnNames, columnWidths);
	}

	/**
	 * 将数据转换为Excel 97-2003 工作簿
	 *
	 * @param <T> T extends Collection&lt;?&gt; or T extends Object[]
	 * @param data
	 *        Excel数据
	 * @param filepath
	 *        Excel文件绝对路径
	 * @param sheetName
	 *        工作表名称
	 * @param columnNames
	 *        列名
	 * @param columnWidths
	 *        列宽
	 * @return
	 * 		本地Excel文件对象
	 */
	public static <T> File dataToExcel(Collection<T> data, String filepath, String sheetName, String[] columnNames, int[] columnWidths) {
		File file = new File(filepath);
		File parentFile = file.getParentFile();
		if (!parentFile.exists()) {
			parentFile.mkdirs();
		}

		HSSFWorkbook workbook = null;
		if (file.exists()) {
			try {
				workbook = (HSSFWorkbook) getWorkbook(filepath);
			} catch (IOException e) {}
		} else {
			workbook = new HSSFWorkbook();
		}
		HSSFSheet sheet = createSheet(workbook, sheetName, columnNames, columnWidths);

		HSSFCellStyle normalCellStyle = getNormalCellStyle(workbook);
		HSSFRow row = null;
		HSSFCell cell = null;
		int startRow = 1;
		if (columnNames == null || columnNames.length == 0) {
			startRow = 0;
		}
		if (!data.isEmpty()) {
			Class<?> clazz = data.iterator().next().getClass();
			if (Collection.class.isAssignableFrom(clazz)) {
				for (T rowList : data) {
					row = sheet.createRow(startRow++);
					int i = 0;
					for (Object t : (Collection<?>) rowList) {
						cell = row.createCell(i++);
						cell.setCellStyle(normalCellStyle);
						if (t != null) {
							cell.setCellValue(String.valueOf(t));
						}
					}
				}
			} else if (Object[].class.isAssignableFrom(clazz)) {
				for (T rowList : data) {
					row = sheet.createRow(startRow++);
					int i = 0;
					for (Object t : (Object[]) rowList) {
						cell = row.createCell(i++);
						cell.setCellStyle(normalCellStyle);
						if (t != null) {
							cell.setCellValue(String.valueOf(t));
						}
					}
				}
			} else {
				throw new IllegalArgumentException("<T> T extends Collection<?> or T extends Object[]");
			}
		}
		OutputStream stream = null;
		try {
			stream = new FileOutputStream(file);
			workbook.write(stream);
			stream.flush();
			stream.close();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		return file;
	}

	// #############################
	// #######Excel操作辅助方法######
	// #############################

	private static final SimpleDateFormat SDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.sss");

	/**
	 * 根据文件路径获取Excel 97-2003 工作簿对象
	 *
	 * @param filepath
	 *        Excel 97-2003 工作簿文件绝对路径
	 * @return 如果文件存在，则读取工作簿；不存在则创建一个新的工作簿对象
	 * @throws IOException
	 *         当文件无法读取时抛出
	 * @throws IllegalArgumentException
	 *         filepath表示的不是一个Excel工作簿文件
	 */
	public static Workbook getWorkbook(String filepath)
			throws IOException {
		String name = filepath.toLowerCase();
		if (name.endsWith(".xls")) {
			File file = new File(filepath);
			URL url = file.toURL();
//			URL url = new URL(filepath);
			URLConnection connection = url.openConnection();
			InputStream is = connection.getInputStream();
			return new HSSFWorkbook(is);
//			return new HSSFWorkbook(new FileInputStream(new File(filepath)));
		} else if (name.endsWith(".xlsx")) {
			File file = new File(filepath);
			URL url = file.toURL();
//			URL url = new URL(filepath);
			URLConnection connection = url.openConnection();
			InputStream is = connection.getInputStream();
			return new XSSFWorkbook(is);
//			return null;
		} else {
			throw new IllegalArgumentException("请指定一个Excel工作簿文件");
		}
	}


	public static Sheet getSheet(String filepath, String sheetName)
			throws IOException {
		return getWorkbook(filepath).getSheet(sheetName);
	}

	public static Sheet getSheet(String filepath, int sheetIndex)
			throws IOException {
		return getWorkbook(filepath).getSheetAt(sheetIndex);
	}

	public static Sheet getSheet(MultipartFile file, String sheetName)
			throws IOException {
//		//判断excel文件是03版本还是07版本
//		String extention = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);
//		// 获取文件名
//		String fileName = file.getOriginalFilename();
//		// 获取文件后缀
//		String prefix=fileName.substring(fileName.lastIndexOf("."));
//		final File excelFile = File.createTempFile(UUID.randomUUID().toString(), prefix);
//		// MultipartFile to File
//		file.transferTo(excelFile);
		//这个是07版本
		XSSFWorkbook workbook = null;
		try {
			workbook = new XSSFWorkbook(new FileInputStream(MultipartFileToFile.multipartFileToFile(file)));
		} catch (Exception e) {
			e.printStackTrace();
		}
		//这个是03版本
		//HSSFWorkbook  workbook2 = new HSSFWorkbook();
		//获取文件中的表格
		Sheet sheet = workbook.getSheet(sheetName);
		return sheet;
	}

	/**
	 * 根据名称创建工作表
	 *
	 * @param workbook
	 *        工作簿
	 * @param sheetName
	 *        工作表名称
	 * @param columnNames
	 *        列名，可以为空
	 * @param columnWidths
	 *        列宽，可以为空
	 * @return
	 */
	public static HSSFSheet createSheet(HSSFWorkbook workbook, String sheetName, String[] columnNames, int[] columnWidths) {
		HSSFFont font = workbook.getFontAt((short) 0);
		font.setFontName("宋体");// 设置默认字体名称
		font.setFontHeightInPoints((short) 12);// 设置默认字体大小

		HSSFSheet sheet = workbook.createSheet(sheetName);
		sheet.setDefaultColumnWidth(12);// 设置工作表默认列宽
		sheet.setDefaultRowHeightInPoints(13.5f);// 设置工作表默认行高

		HSSFCellStyle headerCellStyle = getNormalCellStyle(workbook);

		HSSFRow header = null;

		// 设置列名
		if (columnNames != null) {
			header = sheet.createRow(0);
			header.setHeightInPoints(15f);// 设置标题行高
			for (int i = 0; i < columnNames.length; i++) {
				HSSFCell cell = header.createCell(i);
				cell.setCellValue(columnNames[i]);
				cell.setCellStyle(headerCellStyle);
			}
		}

		// 设置列宽
		if (columnWidths != null) {
			for (int i = 0; i < columnWidths.length; i++) {
				sheet.setColumnWidth(i, columnWidths[i]);
			}
		}

		return sheet;
	}

	/**
	 * 获取Excel单元格值，支持所有单元格类型
	 */
	public static String getCellValue(Cell cell) {
		if (cell == null) {
			return null;
		}
		Object value = null;
		int cellType = cell.getCellType();
		if (cellType == Cell.CELL_TYPE_BLANK) {} else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
			value = cell.getBooleanCellValue();
		} else if (cellType == Cell.CELL_TYPE_ERROR) {
			value = cell.getErrorCellValue();
		} else if (cellType == Cell.CELL_TYPE_STRING) {
			value = cell.getRichStringCellValue();
		} else if (cellType == Cell.CELL_TYPE_NUMERIC) {
			if (DateUtil.isCellDateFormatted(cell)) {
				value = SDF.format(cell.getDateCellValue());
			} else {
				cell.setCellType(Cell.CELL_TYPE_STRING);
				value = cell.getRichStringCellValue();
				// cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			}
		} else if (cellType == Cell.CELL_TYPE_FORMULA) {
			try {
				if (DateUtil.isCellDateFormatted(cell)) {
					value = SDF.format(cell.getDateCellValue());
				} else {
					cell.setCellType(Cell.CELL_TYPE_STRING);
					value = cell.getRichStringCellValue();
					cell.setCellType(Cell.CELL_TYPE_NUMERIC);
				}
			} catch (IllegalStateException e) {
				value = cell.getRichStringCellValue();
			}
		}
		if (value == null) {
			return null;
		} else {
			if (value instanceof String || value instanceof RichTextString) {
				String str = value.toString().replaceAll("(^[\\pZ\\s]+)|([\\pZ\\s]+$)", "");
				return str.isEmpty() ? null : str;
			} else {
				return value.toString();
			}
		}
	}

	public static HSSFCellStyle getNormalCellStyle(HSSFWorkbook workbook) {
		HSSFCellStyle normalCellStyle = workbook.createCellStyle();

		HSSFFont normalFont = workbook.createFont();// 设置字体大小
		normalFont.setFontName("宋体");// 设置字体名称
		normalFont.setFontHeightInPoints((short) 11);// 设置字体大小
		// normalFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置字体加粗
		normalCellStyle.setFont(normalFont);// 设置单元格字体

		normalCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 设置上边框
		normalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 设置下边框
		normalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 设置左边框
		normalCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 设置右边框

		normalCellStyle.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);// 设置上边框颜色
		normalCellStyle.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);// 设置下边框颜色
		normalCellStyle.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);// 设置左边框颜色
		normalCellStyle.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);// 设置右边框颜色

		normalCellStyle.setFillBackgroundColor(HSSFColor.WHITE.index);// 设置背景色
		normalCellStyle.setFillForegroundColor(HSSFColor.WHITE.index);// 设置前景色
		normalCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充模式

		// normalCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置水平对齐方式
		normalCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 设置水平对齐方式
		normalCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置垂直对齐方式
		normalCellStyle.setDataFormat(workbook.createDataFormat().getFormat("@"));// 设置单元格为文本格式
		// normalCellStyle.setWrapText(true);// 设置自动换行，当文本中出现换行符时

		return normalCellStyle;
	}

}

